<?php
class MssqlBase{
	const RESULT_FIELD_NAME_LOWER = false; 
	
	var $conn = false;
	
	function __construct($dbHost,$dbUser,$dbPassword,$dbName,$charset='gbk'){
		
		if(!function_exists('mssql_pconnect'))JYTools::error('PHP环境未安装MSSQL函数库！');
		//$linkfunction = ( TRUE == $dbConfig['persistent'] ) ? 'mssql_pconnect' : 'mssql_connect';

		$this->conn = mssql_connect($dbHost, $dbUser, $dbPassword) or JYTools::error("数据库链接错误 : " . mssql_get_last_message()); 
		mssql_select_db($dbName, $this->conn) or JYTools::error("无法找到数据库，请确认数据库名称正确！");
        if (!$this->conn){
           JYTools::error('数据库存连接失败！');	
           return;
        }
	}

	function execute($sql){
		//貌似本身有一个mssql_execute用来执行存储过程的
		$stmt = &$this->_execute($sql);		
	
		$rs = $stmt? mssql_rows_affected($this->conn):-1;
		if($stmt instanceof mssql_result) mssql_free_result($stmt);
		return $rs;
	}

	function query($sql, $page = 0, $pageSize = 10,&$totalCount=0){
		//exit("query:".$page);
		//不分页
		if(!$page){
			$stmt = &$this->_execute($sql);
			$data = $this->fetchArray($stmt);
			$totalCount= count($data);
			return $data;
		}
		
		$sqlCount = "select count(1) cnt  from (".$sql.") t";
		$totalCount = $this->query($sqlCount);	
		$totalCount = $totalCount[0]['cnt'];
		
		//总页数
		$lastPage = ceil($totalCount/$pageSize);

		//exit($lastPage);
		//第一页或中间页
		if($page<$lastPage){
			$sql = "select top ".($page*$pageSize)." * from ({$sql}) t";
		}

		$stmt = &$this->_execute($sql);
		$offset = ($page-1)*$pageSize;
			
		$data = $this->fetchArray($stmt,$offset,$pageSize);
		return $data;
	}

	function find($table,$where,$fields='*',$order=''){
		$row = $this->findAll($table,$where,$fields,$order,1,1);
		return $row[0];
	}

	function findAll($table,$where='',$fields='*',$order='', $page = 0, $pageSize = 10,&$totalCount=0){
		$sql = "select {$fields}  from ".$table." where 1=1 "
		.($where?" and ".$where:"")
		.($order?" order by ".$order:"");
		
		return $this->query($sql,$page, $pageSize,&$totalCount);
	}

	function update($table,$where,$row){
	 	$string = $this->compile_update_string ( $row );
        $sql = 'UPDATE ' . $table . ' SET ' . $string.' WHERE ' . $where;
 
        return $this->execute($sql);
	}

	function create($table,$row,$pkField=''){
		$formated_array = $this->compile_insert_string($row);
        $sql = 'INSERT INTO ' . $table . ' (' . $formated_array ['FIELD_NAMES'] . ') VALUES(' . $formated_array ['FIELD_VALUES'] . ')';
		$stmt = &$this->_execute($sql);
		$nowid = $this->getNewId($table);
		$nowid = $nowid?$nowid:1;
		return $stmt?$nowid:-1;
	}

	function getNewId($table){
		$result = $this->query("select @@IDENTITY as NEWID");
		
		$newid = $result[0]['NEWID'];
		
		if($newid) return $newid; 
	}
	
	function del($table,$where){
		if(is_array($where)){
			$where = $this->compile_update_string ( $where );
		}
        $sql = "DELETE FROM ".$table." WHERE ".$where;
 
        return $this->execute($sql);
	}
	
	/*执行SQL
	参数:
	@params $sql string
	@params $row array 
	*/
	private function &_execute($sql){
		if(empty($this->conn)){
           JYTools::error("Not connect database");
        }
        
		JYTools::log($sql);
        $result = mssql_query ($sql, $this->conn);
        
        if($result === false){
			JYTools::error($sql.": Query String error." . mysqli_error($this->conn));
        }
        
       	return $result;
	}

//	 /**
//     * 进行限定记录集的查询
//     *
//     * @param string $sql
//     * @param int $length
//     * @param int $offset		
//     *
//     * @return resource
//     */
//    private function selectLimit($sql, $offset = null,$length = null){
//		$intPageSize = intval($length)>0?intval($length):0;
//		$intStartPosition = intval($offset)>0?intval($offset):0;
//		
//		$strSQL = $sql;
//		$strPattern = '/^\s*(SELECT\s+(ALL|DISTINCT)?(\s+TOP\s+\d+)?(.+))(\s+FROM\s+.+)';
//		if(strripos($strSQL, 'WHERE')){
//			$strPattern .= '(\s+WHERE\s+.+)';
//		}
//		if(strripos($strSQL, 'GROUP BY')){
//			$strPattern .= '(\s+GROUP BY\s+.+)';
//		}
//		if(strripos($strSQL, 'HAVING')){
//			$strPattern .= '(\s+HAVING\s+.+)';
//		}
//		if(strripos($strSQL, 'ORDER BY')){
//			$strPattern .= '(\s+ORDER BY\s+.+)';
//		}
//		$strPattern .= '$/i';
//		$arrMatches = array();
//		if(preg_match($strPattern, $strSQL, $arrMatches)){
//			$j = count($arrMatches);
//			for($i = 0; $i < $j; $i ++){
//				$arrMatches[$i] = trim($arrMatches[$i]);
//			}
//			if(empty($arrMatches[3]) && $j > 5){
//				$strLimitSql = 'SELECT ' . $arrMatches[2] . ' TOP ' . $intPageSize . ' ' . $arrMatches[4];
//				$strLimitSql .= ' ' . $arrMatches[5];
//				$strAlias = '';
//				if(strpos($arrMatches[5], ',')){
//					$strAlias = substr($arrMatches[5], 0, strpos($arrMatches[5], ','));
//				}elseif(stristr($arrMatches[5], " JOIN ")){
//					$strAlias = stristr($arrMatches[5], " JOIN ");
//					$strAlias = substr($arrMatches[5], 0, strpos($arrMatches[5], $strAlias));
//				}
//				if(! empty($strAlias)){
//					$strAlias = trim(substr($strAlias, 4));
//					$arrAlias = split(' ', $strAlias);
//					$strAlias = $arrAlias[0];
//					if(strtoupper($arrAlias[1]) == 'AS'){
//						$strAlias = $arrAlias[2];
//					}elseif(! in_array(strtoupper($arrAlias[1]), array('INNER','LEFT','JOIN','RIGHT','FULL'))){
//						$strAlias = $arrAlias[1];
//					}
//					//$strAlias = trim(substr($strAlias, strrpos($strAlias, ' ')));
//					if(! empty($strAlias))$strAlias .= '.';
//				}
//				if($j > 6){
//					if(strtoupper(substr($arrMatches[6], 0, 5)) == 'WHERE'){
//						$strLimitSql .= ' WHERE (' . substr($arrMatches[6], 5) . ') AND ' . $strAlias . 'IDENTITYCOL NOT IN (';
//						$strLimitSql .= 'SELECT ' . $arrMatches[2] . ' TOP ' . $intStartPosition . ' ' . $strAlias . 'IDENTITYCOL ' . $arrMatches[5];
//						for($i = 6; $i < $j; $i ++){
//							$strLimitSql .= ' ' . $arrMatches[$i];
//						}
//						$strLimitSql .= ')';
//						for($i = 7; $i < $j; $i ++){
//							$strLimitSql .= ' ' . $arrMatches[$i];
//						}
//					}else{
//						$strLimitSql .= ' WHERE ' . $strAlias . 'IDENTITYCOL NOT IN (';
//						$strLimitSql .= 'SELECT ' . $arrMatches[2] . ' TOP ' . $intStartPosition . ' ' . $strAlias . 'IDENTITYCOL ' . $arrMatches[5];
//						for($i = 6; $i < $j; $i ++){
//							$strLimitSql .= ' ' . $arrMatches[$i];
//						}
//						$strLimitSql .= ')';
//						for($i = 6; $i < $j; $i ++){
//							$strLimitSql .= ' ' . $arrMatches[$i];
//						}
//					}
//				}else{
//					$strLimitSql .= ' WHERE ' . $strAlias . 'IDENTITYCOL NOT IN (';
//					$strLimitSql .= 'SELECT ' . $arrMatches[2] . ' TOP ' . $intStartPosition . ' ' . $strAlias . 'IDENTITYCOL ' . $arrMatches[5];
//					$strLimitSql .= ')';
//				}
//				return $strLimitSql;
//			}
//			return false;
//		}
//		return false;
//	}


//function _adodb_getcount(&$zthis, $sql,$inputarr=false,$secs2cache=0){
//	$qryRecs = 0;
//	
//	 if (!empty($zthis->_nestedSQL) || preg_match("/^\s*SELECT\s+DISTINCT/is", $sql) || 
//	 	preg_match('/\s+GROUP\s+BY\s+/is',$sql) || 
//		preg_match('/\s+UNION\s+/is',$sql)) {
//		
//		$rewritesql = adodb_strip_order_by($sql);
//		
//		// ok, has SELECT DISTINCT or GROUP BY so see if we can use a table alias
//		// but this is only supported by oracle and postgresql...
//		if ($zthis->dataProvider == 'oci8') {
//			// Allow Oracle hints to be used for query optimization, Chris Wrye
//			if (preg_match('#/\\*+.*?\\*\\/#', $sql, $hint)) {
//				$rewritesql = "SELECT ".$hint[0]." COUNT(*) FROM (".$rewritesql.")"; 
//			} else
//				$rewritesql = "SELECT COUNT(*) FROM (".$rewritesql.")"; 
//			
//		} else if (strncmp($zthis->databaseType,'postgres',8) == 0 || strncmp($zthis->databaseType,'mysql',5) == 0)  {
//			$rewritesql = "SELECT COUNT(*) FROM ($rewritesql) _ADODB_ALIAS_";
//		} else {
//			$rewritesql = "SELECT COUNT(*) FROM ($rewritesql)";
//		}
//	} else {
//		// now replace SELECT ... FROM with SELECT COUNT(*) FROM
//		$rewritesql = preg_replace(
//					'/^\s*SELECT\s.*\s+FROM\s/Uis','SELECT COUNT(*) FROM ',$sql);
//		// fix by alexander zhukov, alex#unipack.ru, because count(*) and 'order by' fails 
//		// with mssql, access and postgresql. Also a good speedup optimization - skips sorting!
//		// also see http://phplens.com/lens/lensforum/msgs.php?id=12752
//		$rewritesql = adodb_strip_order_by($rewritesql);
//	}
//	
//	if (isset($rewritesql) && $rewritesql != $sql) {
//		if (preg_match('/\sLIMIT\s+[0-9]+/i',$sql,$limitarr)) $rewritesql .= $limitarr[0];
//		 
//		if ($secs2cache) {
//			// we only use half the time of secs2cache because the count can quickly
//			// become inaccurate if new records are added
//			$qryRecs = $zthis->CacheGetOne($secs2cache/2,$rewritesql,$inputarr);
//			
//		} else {
//			$qryRecs = $zthis->GetOne($rewritesql,$inputarr);
//	  	}
//		if ($qryRecs !== false) return $qryRecs;
//	}
//	//--------------------------------------------
//	// query rewrite failed - so try slower way...
//	
//	
//	// strip off unneeded ORDER BY if no UNION
//	if (preg_match('/\s*UNION\s*/is', $sql)) $rewritesql = $sql;
//	else $rewritesql = $rewritesql = adodb_strip_order_by($sql); 
//	
//	if (preg_match('/\sLIMIT\s+[0-9]+/i',$sql,$limitarr)) $rewritesql .= $limitarr[0];
//		
//	if ($secs2cache) {
//		$rstest = $zthis->CacheExecute($secs2cache,$rewritesql,$inputarr);
//		if (!$rstest) $rstest = $zthis->CacheExecute($secs2cache,$sql,$inputarr);
//	} else {
//		$rstest = $zthis->Execute($rewritesql,$inputarr);
//		if (!$rstest) $rstest = $zthis->Execute($sql,$inputarr);
//	}
//	if ($rstest) {
//	  		$qryRecs = $rstest->RecordCount();
//		if ($qryRecs == -1) { 
//		global $ADODB_EXTENSION;
//		// some databases will return -1 on MoveLast() - change to MoveNext()
//			if ($ADODB_EXTENSION) {
//				while(!$rstest->EOF) {
//					adodb_movenext($rstest);
//				}
//			} else {
//				while(!$rstest->EOF) {
//					$rstest->MoveNext();
//				}
//			}
//			$qryRecs = $rstest->_currentRow;
//		}
//		$rstest->Close();
//		if ($qryRecs == -1) return 0;
//	}
//	return $qryRecs;
//}
//
//function adodb_strip_order_by($sql)
//{
//	$rez = preg_match('/(\sORDER\s+BY\s[^)]*)/is',$sql,$arr);
//	if ($arr)
//		if (strpos($arr[0],'(') !== false) {
//			$at = strpos($sql,$arr[0]);
//			$cntin = 0;
//			for ($i=$at, $max=strlen($sql); $i < $max; $i++) {
//				$ch = $sql[$i];
//				if ($ch == '(') {
//					$cntin += 1;
//				} elseif($ch == ')') {
//					$cntin -= 1;
//					if ($cntin < 0) {
//						break;
//					}
//				}
//			}
//			$sql = substr($sql,0,$at).substr($sql,$i);
//		} else
//			$sql = str_replace($arr[0], '', $sql); 
//	return $sql;
// }


	/*取下一行数据
	返回:成功，返回结果数组
	*/
	private function fetch(&$stmt){
		$row = @mssql_fetch_array($stmt, MSSQL_ASSOC);
        if ($row && MssqlBase::RESULT_FIELD_NAME_LOWER) {
            $row = array_change_key_case($row, CASE_LOWER);
        }
		return $row;
	}

	/*
	*
	*/
	private function fetchArray(&$stmt,$offset=0,$rows=-1){
		//移动游标
		if($offset){
			mssql_data_seek($stmt,$offset);
		}

		$data = array();
		while($row=$this->fetch($stmt)){
			$data[]= $row;
			if($rows>0&&count($data)>=$rows) break;
		}

		if($stmt instanceof mssql_result) mssql_free_result($stmt);
		return $data;
	}
	
 	/**
     * format update set
     *
     * @param array $data
     * @return formated $data
     */
    private function compile_update_string($data){
        $return = '';
        foreach( $data as $k => $v ){
            if (is_string($v)||!$v){
                $return .= "$k='" . $this->formatString ( $v ) . "',";
            }else{
                $return .= "$k=" . $v . ",";
            }
        }
        $return = preg_replace ( "/,$/", "", $return );

        return $return;
    }
    
 /**
     * format  insert set
     *
     * @param array $data
     * @return formated $data
     */
    private function compile_insert_string($data){
        $field_names = '';
        $field_values = '';

        foreach ( $data as $k => $v ){
            $field_names .= "{$k},";

            if (is_string($v)||!$v){
                $field_values .= "'" . $this->formatString ( $v ) . "',";
            }else{
                $field_values .= $v . ",";
            }
        }

        $field_names = preg_replace ( "/,$/", "", $field_names );
        $field_values = preg_replace ( "/,$/", "", $field_values );

        return array ('FIELD_NAMES' => $field_names, 'FIELD_VALUES' => $field_values );
    }

  /**
     * Escapes special characters in a string for use in a SQL statement
     *
     * @param string $str    the query sql string
     * @return string $str   the formatted string
     */
    public function formatString($str){
        if (get_magic_quotes_gpc ()){
            $str = stripslashes ( $str );
        }

        return $str;
    }

	function close(){
	    if($this->conn != null){
            mssql_close ( $this->conn );
            $this->conn = null;
        }
	}

	function __destruct(){
		$this->close();
	} 
}